package com.pentofab.payrollmanagement.database.impl;

public interface SalaryTableSQLConstants extends SQLConstants {

	String selectSalaryProfile = new StringBuffer(select)
		.append(Table_SALARY_PROFILE.description).append(comma)
		.append(Table_SALARY_PROFILE.ProfileId).append(comma)
		.append(Table_SALARY_PROFILE.HRA).append(comma)
		.append(Table_SALARY_PROFILE.Basic).append(comma)
		.append(Table_SALARY_PROFILE.Convy).append(comma)
		.append(Table_SALARY_PROFILE.MedicalAllow).append(comma)
		.append(Table_SALARY_PROFILE.SpecialAllow).append(comma)
		.append(Table_SALARY_PROFILE.maxPF).append(comma)
		.append(Table_SALARY_PROFILE.ESI).append(comma)
		.append(Table_SALARY_PROFILE.pfPercentage).append(comma)
		.append(Table_SALARY_PROFILE.Bonus).append(comma)
		.append(Table_SALARY_PROFILE.others).append(from)
		.append(Table_SALARY_PROFILE.tableName).append(where).append("1=1")
		.toString();
	
	String selectSalaryProfileByID = new StringBuffer(selectSalaryProfile).append(and).append(Table_SALARY_PROFILE.ProfileId)
			.append(equals).append(question).toString();
	
	
	String insertIntoSalaryProfile = new StringBuffer(insert_into).append(Table_SALARY_PROFILE.tableName).append(openingBrace)
	.append(Table_SALARY_PROFILE.description).append(comma)
	.append(Table_SALARY_PROFILE.ProfileId).append(comma)
	.append(Table_SALARY_PROFILE.HRA).append(comma)
	.append(Table_SALARY_PROFILE.Basic).append(comma)
	.append(Table_SALARY_PROFILE.Convy).append(comma)
	.append(Table_SALARY_PROFILE.MedicalAllow).append(comma)
	.append(Table_SALARY_PROFILE.SpecialAllow).append(comma)
	.append(Table_SALARY_PROFILE.maxPF).append(comma)
	.append(Table_SALARY_PROFILE.ESI).append(comma)
	.append(Table_SALARY_PROFILE.pfPercentage).append(comma)
	.append(Table_SALARY_PROFILE.Bonus).append(comma)
	.append(Table_SALARY_PROFILE.others).append(closingBrace)
	.append(values).append(openingBrace).append("?,?,?,?,?,?,?,?,?,?,?,?").append(closingBrace).toString();
	
	String updateSalaryProfile = new StringBuffer(update).append(Table_SALARY_PROFILE.tableName).append(set)
			.append(Table_SALARY_PROFILE.description).append(equals).append(question).append(comma)
			.append(Table_SALARY_PROFILE.HRA).append(equals).append(question).append(comma)
			.append(Table_SALARY_PROFILE.Basic).append(equals).append(question).append(comma)
			.append(Table_SALARY_PROFILE.Convy).append(equals).append(question).append(comma)
			.append(Table_SALARY_PROFILE.MedicalAllow).append(equals).append(question).append(comma)
			.append(Table_SALARY_PROFILE.SpecialAllow).append(equals).append(question).append(comma)
			.append(Table_SALARY_PROFILE.maxPF).append(equals).append(question).append(comma)
			.append(Table_SALARY_PROFILE.ESI).append(equals).append(question).append(comma)
			.append(Table_SALARY_PROFILE.pfPercentage).append(equals).append(question).append(comma)
			.append(Table_SALARY_PROFILE.Bonus).append(equals).append(question).append(comma)
			.append(Table_SALARY_PROFILE.others).append(equals).append(question).append(where)
			.append(Table_SALARY_PROFILE.ProfileId).append(equals).append(question).toString();
	
	String getEMPIDFORSALARYPROFLE = "select sr.employeeID from salarystructure sr join salaryprofile sp on (sp.ProfileId = sr.profileId)";
	
	String deleteSalaryProfile = "delete from salaryprofile where  ProfileId = ?";
	interface Table_SALARY_PROFILE{
		
		String tableName = "salaryprofile";
		String description = tableName+".Description";
		String ProfileId = tableName+".ProfileId";
		String HRA = tableName+".HRA";
		String Basic = tableName+".Basic";
		String Convy = tableName+".Convy";
		String MedicalAllow = tableName+".MedicalAllow";
		String SpecialAllow = tableName+".SpecialAllow";
		String maxPF = tableName+".maxPF";
		String ESI = tableName+".ESI";
		String pfPercentage = tableName+".pfPercentage";
		String Bonus = tableName+".Bonus";
		String others = tableName+".others";
		
	}
	
	String selectSalaryadvance = new StringBuffer(select)
	.append(Table_SALARY_ADVANCE.employeeid).append(comma)
	.append(Table_SALARY_ADVANCE.advance).append(comma)
	.append(Table_SALARY_ADVANCE.noofEMI).append(comma)
	.append(Table_SALARY_ADVANCE.startDate).append(comma)
	.append(Table_SALARY_ADVANCE.Balance).append(comma)
	.append(Table_SALARY_ADVANCE.rownum).append(from)
	.append(Table_SALARY_ADVANCE.tableName).append(where).append("1=1").append(and)
	.append(Table_SALARY_ADVANCE.Balance).append(SQLConstants.greaterthan).append(String.valueOf(new Double(0.0))).toString();
	
	

	  String selectSalaryadvanceByemployeeid = new StringBuffer(selectSalaryadvance).append(and).append(Table_SALARY_ADVANCE.employeeid)
			.append(equals).append(question).toString();

	
	  
	  String insertIntoSalaryadvance = new StringBuffer(insert_into).append(Table_SALARY_ADVANCE.tableName).append(openingBrace)
			.append(Table_SALARY_ADVANCE.employeeid).append(comma)
			.append(Table_SALARY_ADVANCE.advance).append(comma)
			.append(Table_SALARY_ADVANCE.noofEMI).append(comma)
			.append(Table_SALARY_ADVANCE.startDate).append(comma)
			.append(Table_SALARY_ADVANCE.Balance).append(closingBrace)
			.append(values).append(openingBrace).append("?,?,?,?,?").append(closingBrace).toString();
			
	  String updateSalaryAdvance = "update salaryadvance set Balance = Balance - ? where employeeid = ? and rownum = ?"; 
			
	String getPTSlab =  new StringBuffer(select).append(TABLE_PT_SLAB.maxRange).append(comma).append(TABLE_PT_SLAB.ptAmount)
			.append(from).append(TABLE_PT_SLAB.tableName).append(where).append("1=1").toString();
	
	String getPTSlabByMAXRabge = new StringBuffer(and).append(TABLE_PT_SLAB.maxRange).append(equals).append(question).toString();
	
	String deletePTSlab =new StringBuffer(delete).append(from).append(TABLE_PT_SLAB.tableName)
			.append(where).append(TABLE_PT_SLAB.maxRange).append(equals).append(question).toString();
	
	String addPTSlab = new StringBuffer(insert_into).append(TABLE_PT_SLAB.tableName).append(openingBrace)
			.append(TABLE_PT_SLAB.ptAmount).append(comma).append(TABLE_PT_SLAB.maxRange).append(closingBrace)
			.append(values).append(openingBrace).append("?,?").append(closingBrace).toString();
			
	String updatePTSlab = new StringBuffer(update).append(TABLE_PT_SLAB.tableName).append(set)
			.append(TABLE_PT_SLAB.ptAmount).append(equals).append(question).append(comma)
			.append(TABLE_PT_SLAB.maxRange).append(equals).append(question)
			.append(where)
			.append(TABLE_PT_SLAB.ptAmount).append(equals).append(question).append(and)
			.append(TABLE_PT_SLAB.maxRange).append(equals).append(question).append(and).toString();
			
	
	String insertIntoSalaryStracture = "INSERT INTO payroll.salarystructure(employeeID,grossSalary,basic,HRA,conveyance,specialAllowance,"
			+ "medicalAllowance,others,pfrequired,voluntary,excludingBase,employeeCont,employeerCont,pfnumber,esirequired,esinumber,el,cl,sl,bankCode,accountNo)	"
			+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
	
	String updateSalaryStracture = "UPDATE payroll.salarystructure set grossSalary = ? ,basic = ? ,HRA = ? ,conveyance = ? ,specialAllowance = ? ,"
			+ "medicalAllowance = ? ,others = ?, pfrequired = ?,voluntary = ? ,excludingBase =? ,employeeCont = ? ,employeerCont = ? ,pfnumber = ? ,"
			+ "esirequired = ? ,esinumber = ? ,el = ? ,cl = ? ,sl = ?,bankCode = ?,accountNo = ? WHERE employeeID = ? ";
	
	String updateSalaryStractureDuringProfileModify = "UPDATE payroll.salarystructure SET  basic = ? ,  HRA = ? ,  conveyance = ?,  "
			+ "specialAllowance = ? ,  medicalAllowance = ? , PFAmount = ? , "
			+ " ESIAmount = ? ,  bonus = ? ,  pfPerCnt = ? ,  others = ?  WHERE profileId = ? ";
	
	String getSalaryStractureByEmp = "SELECT salarystructure.employeeID , salarystructure.grossSalary , salarystructure.basic , "
			+ "salarystructure.HRA , salarystructure.conveyance , salarystructure.specialAllowance , salarystructure.medicalAllowance,salarystructure.others ,"
			+ "salarystructure.pfrequired,salarystructure.excludingBase,salarystructure.voluntary,salarystructure.employeeCont,salarystructure.employeerCont,salarystructure.pfnumber,"
			+ "salarystructure.esirequired,salarystructure.esinumber,salarystructure.bankCode,salarystructure.accountNo,salarystructure.el,salarystructure.cl,salarystructure.sl  FROM salarystructure where employeeID= ? ";
	
	String getAllEmpSalaryStracture = "SELECT salarystructure.employeeID,employee.unitcode,employee.name,employee.companycode,"
			+ "employee.deptcode,employee.category,employee.hoursperday , salarystructure.grossSalary , salarystructure.basic , "
			+ "salarystructure.HRA , salarystructure.conveyance , salarystructure.specialAllowance , salarystructure.medicalAllowance,salarystructure.others ,"
			+ "salarystructure.pfrequired,salarystructure.excludingBase,salarystructure.voluntary,salarystructure.employeeCont,salarystructure.employeerCont,salarystructure.pfnumber,"
			+ "salarystructure.esirequired,salarystructure.esinumber,salarystructure.bankCode,salarystructure.accountNo,salarystructure.el,salarystructure.cl,salarystructure.sl "
			+ " FROM salarystructure JOIN employee ON(salarystructure.employeeID  = employee.employeeid AND employee.deletiondate = '') ";
	
	String deleteSalaryadvance = "delete from salaryadvance where employeeid=? and rownum=?"; 
	
	
	String getSalaryRport ="SELECT emp.employeeid AS EMPID, emp.name AS NAME, emp.Companycode AS Company,emp.Deptcode AS Dept, "
			+ "emp.Designationcode Design, str.ESINumber AS ESINUMBER,str.PFNumber AS PFNUMBER, '%s' AS MaxDays, '%s' AS PAIDDAYS,str.grossSalary AS GROSS, "
			+ "(str.grossSalary * (str.basic/100)) AS basic,(str.grossSalary * (str.hra/100)) AS hra, (str.grossSalary * (str.conveyance/100)) AS conveyance,"
			+ "(str.grossSalary * (str.specialAllowance/100)) AS specialAllowance,(str.grossSalary * (str.medicalAllowance/100)) AS medicalAllowance,"
			+ " (str.grossSalary * (str.others/100)) AS Others,(str.grossSalary * (str.bonus/100)) AS bonus, '%s' AS INCENTIVE,"
			+ "(CASE WHEN str.pfPerCnt='0.00' THEN str.PFAmount ELSE (str.grossSalary * (str.pfPerCnt/100)) END) AS PF, "
			+ " (str.grossSalary * (str.ESIAmount/100)) AS ESI,  "
			+ "(SELECT SUM(advance/noofEmi) FROM salaryadvance adv WHERE CURDATE() <= STR_TO_DATE(adv.enddate,'%d-%m-%Y') AND ADV.employeeId = EMPID ) AS SalAdv,"
			+ "  (str.grossSalary *  (str.basic+str.hra+str.conveyance+str.specialAllowance+str.medicalAllowance+str.Others)/100) AS Earnings,"
			+ "(SELECT (PF+ESI+(CASE WHEN SalAdv IS NULL THEN 0 ELSE SalAdv END)) FROM DUAL )AS DECUCATION   FROM  "
			+ " employee emp JOIN salarystructure str ON (str.employeeID = emp.employeeid)  WHERE emp.deletionDate = '' "
			+ "and emp.employeeid in (select distinct EmployeeId from attendance union select distinct EID from leave_system)"
				
			;
	String getSalaryRportForEMp = getSalaryRport+" and emp.employeeid = ?";
	
	String getESIPFBonus = "select ESI,PF,BONUS from ESIPFBONUS";
	String setESIPFBonus = "update ESIPFBONUS set ESI = ?, PF =?, Bonus =?";
	
	
	
	String getPtSlab = "SELECT min(ptAmount) FROM ptslab WHERE   ? < maxrange";
	interface Table_SALARY_ADVANCE{
		
		String tableName = "salaryadvance";
		String employeeid = tableName+".employeeid";
		String advance = tableName+".advance";
		String noofEMI = tableName+".noofEMI";
		String startDate = tableName+".startDate";
		String rownum = tableName+".rownum";
		String Balance = tableName+".Balance";
		
	}
	
	interface TABLE_SALARY_STRACTUR{
		
		String tableName = "salarystructure";
		
		String employeeID = tableName+".employeeID";
		String grossSalary = tableName+".grossSalary"; 
		String basic = tableName+".basic";
		String HRA = tableName+".HRA"; 
		String conveyance = tableName+".conveyance"; 
		String specialAllowance = tableName+".specialAllowance"; 
		String medicalAllowance = tableName+".medicalAllowance";
		String others = tableName+".others";
		String pfrequired = tableName+".pfrequired"; 
		String voluntary= tableName+".voluntary";  
		String employeeCont = tableName+".employeeCont";  
		String employeerCont = tableName+".employeerCont"; 
		String pfnumber = tableName+".pfnumber";  
		String esirequired = tableName+".esirequired"; 
		String esinumber = tableName+".esinumber";  
		String el = tableName+".el"; 
		String cl = tableName+".cl"; 
		String sl  = tableName+".sl";
		String excludingBase = tableName+".excludingBase";
		String accountNo = tableName+".accountNo";
		String bankCode = tableName+".bankCode";
	}
	
	String salaryReportConms = "Emp Id,Name,Company,Unit,Dept,Design,Gross,Basic,HRA,Convy(CA),Spl All(CCA),Medic Allow,Others,Incentive,Total Gross,PF Number,PF Amount,"
			+ "ESI Number,ESI Amount,PT,Advance,TDS,Fines,Others,Desuctions,Net Salary";
	
	String getPaidSalaryInfo = "SELECT EMPID,NAME,Company,Dept,"
			+ "Unit,Design,ESINumber,PFNumber,MaxDays,PaidDays,Gross,Basic,HRA,Conveyance,Special,Medic,Others,"
			+ "INCENTIVE,PF,ESI,PTax,LOP,Earnings,DECUCATION,NetPay FROM payroll.paidsalaryinfo where MONTH = ? and YEAR = ? ";
	
	String updatePaidsalaryinfo = "update into payroll.paidsalaryinfo set PaidDays = ?,Gross = ?,Basic = ?,HRA = ?,"
			+ "Conveyance = ?,Special = ?,Medic = ?,Others = ?,INCENTIVE = ?,"
			+ "PF = ?,ESI = ?,PTax = ?,LOP = ?,Earnings = ?,DECUCATION = ?,NetPay = ? "
			+ "where EMPID = ? and MONTH = ? and YEAR = ?"; 
	
	String insertOrUpdatePaidSalaryInfo = "insert into payroll.paidsalaryinfo(EMPID,MONTH,YEAR,NAME,Company,Dept,Unit,"
			+ "Design,ESINumber,PFNumber,MaxDays,PaidDays,Gross,Basic,HRA,Conveyance,Special,"
			+ "Medic,Others,INCENTIVE,PF,ESI,PTax,LOP,Earnings,DECUCATION,NetPay) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) "
			+ " ON DUPLICATE KEY UPDATE PaidDays = ?,Gross = ?,Basic = ?,HRA = ?,"
			+ "Conveyance = ?,Special = ?,Medic = ?,Others = ?,INCENTIVE = ?,"
			+ "PF = ?,ESI = ?,PTax = ?,LOP = ?,Earnings = ?,DECUCATION = ?,NetPay = ?";
	interface TABLE_PT_SLAB{
		
		String tableName = "ptslab";
		String maxRange = tableName+".maxRange";
		String ptAmount = tableName+".ptAmount";
	}
	
	interface TABLE_ESIPFBONUS{
		String tableName = "ESIPFBONUS";
		String ESI = "ESI";
		String PF = tableName+".PF";
		String BONUS = tableName+".BONUS";
	}
}
